Imports AUtility
Partial Public Class Tablica
    Inherits System.Web.UI.Page

    Dim selectStr As String



#Region "Get Tablica"


    Private Sub GetTable(ByVal sezonaId As Integer, ByVal ligaId As Integer)
        Dim ligaSession As String = sezonaId.ToString() + "_" + ligaId.ToString()
        Dim ligaDT As DataTable = New DataTable
        If (Application(ligaSession) Is Nothing) Then
            Dim outputString As String = "<B>ImePrezime Odigrane Pobjede Setovi Gemovi Bodovi</B><br/>"
            Dim dt As DataTable = DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, String.Format("SELECT IgracId, ImePrezime, Redosljed FROM vSezonaLigaIgrac WHERE SezonaId = {0} AND LigaId = {1}", sezonaId, ligaId))
            'Dim dt As DataTable = DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, "SELECT IgracId, ImePrezime FROM vIgrac)

            Dim odigraneN As Integer
            Dim pobjedeN As Integer
            Dim gemoviD As Integer
            Dim gemoviI As Integer
            Dim setoviD As Integer
            Dim setoviI As Integer


            ligaDT.Columns.Add("IgracId")
            ligaDT.Columns.Add("ImePrezime")
            ligaDT.Columns.Add("Odigrane")
            ligaDT.Columns.Add("Pobjede")
            ligaDT.Columns.Add("SetoviD", GetType(Integer))
            ligaDT.Columns.Add("SetoviI", GetType(Integer))
            ligaDT.Columns.Add("GemoviD", GetType(Integer))
            ligaDT.Columns.Add("GemoviI", GetType(Integer))
            ligaDT.Columns.Add("Bodovi", GetType(Integer))
            ligaDT.Columns.Add("SetoviRazlika", GetType(Integer), "SetoviD - SetoviI")
            ligaDT.Columns.Add("GemoviRazlika", GetType(Integer), "GemoviD - GemoviI")
            ligaDT.Columns.Add("SetoviDisplay", GetType(String))
            ligaDT.Columns.Add("GemoviDisplay", GetType(String))

            ligaDT.Columns.Add("Redosljed", GetType(Integer))

            For Each row As DataRow In dt.Rows
                Try

                    Dim igracId As Integer = Convert.ToInt32(row("IgracId"))
                    odigraneN = Odigrane(igracId, sezonaId)
                    pobjedeN = Pobjede(igracId, sezonaId)
                    gemoviD = DobiveniGemovi(igracId, sezonaId)
                    gemoviI = IzgubljeniGemovi(igracId, sezonaId)
                    setoviD = DobiveniSetovi(igracId, sezonaId)
                    setoviI = IzgubljeniSetovi(igracId, sezonaId)
                Catch ex As Exception

                End Try

                Dim ligaRow As DataRow
                ligaRow = ligaDT.NewRow()
                ligaRow("IgracId") = row("IgracId")
                ligaRow("ImePrezime") = row("ImePrezime")
                ligaRow("Redosljed") = row("Redosljed")
                ligaRow("Odigrane") = odigraneN
                ligaRow("Pobjede") = pobjedeN
                ligaRow("SetoviD") = setoviD
                ligaRow("SetoviI") = setoviI
                ligaRow("GemoviD") = gemoviD
                ligaRow("GemoviI") = gemoviI
                ligaRow("Bodovi") = odigraneN + 2 * pobjedeN
                ligaRow("SetoviDisplay") = ligaRow("SetoviD").ToString() + ":" + ligaRow("SetoviI").ToString()
                ligaRow("GemoviDisplay") = ligaRow("GemoviD").ToString() + ":" + ligaRow("GemoviI").ToString()


                ligaDT.Rows.Add(ligaRow)

                outputString = outputString + String.Format("{0} {1} {2} {3} {4}:{5} {6}:{7} {8}<br/>", row("ImePrezime"), odigraneN, pobjedeN, "", setoviD, setoviI, gemoviD, gemoviI, odigraneN + 2 * pobjedeN)

                odigraneN = 0
                pobjedeN = 0
                gemoviD = 0
                gemoviI = 0
                setoviD = 0
                setoviI = 0
            Next

            ligaDT.DefaultView.Sort = "Redosljed ASC, Bodovi DESC, Odigrane DESC, SetoviRazlika DESC, GemoviRazlika DESC"
            Application(ligaSession) = ligaDT
        Else
            ligaDT = DirectCast(Application(ligaSession), DataTable)
        End If
        gvLiga.DataSource = ligaDT
        gvLiga.DataBind()

    End Sub


    Private Function DobiveniSetovi(ByVal igracId As Integer, ByVal sezonaId As Integer) As Integer
        selectStr = String.Format("SELECT COUNT(*)  FROM vSusretiProtivnik WHERE(Set1 > ProtivnikSet1)AND IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Dim prviSetovi As Integer = DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
        selectStr = String.Format("SELECT COUNT(*)  FROM vSusretiProtivnik WHERE(Set2 > ProtivnikSet2)AND IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Dim drugiSetovi As Integer = DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
        selectStr = String.Format("SELECT COUNT(*)  FROM vSusretiProtivnik WHERE(Set3 > ProtivnikSet3)AND IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Dim treciSetovi As Integer = DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)

        Return prviSetovi + drugiSetovi + treciSetovi

    End Function

    Private Function IzgubljeniSetovi(ByVal igracId As Integer, ByVal sezonaId As Integer) As Integer
        selectStr = String.Format("SELECT COUNT(*)  FROM vSusretiProtivnik WHERE(Set1 < ProtivnikSet1)AND IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Dim prviSetovi As Integer = DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
        selectStr = String.Format("SELECT COUNT(*)  FROM vSusretiProtivnik WHERE(Set2 < ProtivnikSet2)AND IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Dim drugiSetovi As Integer = DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
        selectStr = String.Format("SELECT COUNT(*)  FROM vSusretiProtivnik WHERE(Set3 < ProtivnikSet3)AND IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Dim treciSetovi As Integer = DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)

        Return prviSetovi + drugiSetovi + treciSetovi

    End Function


    Private Function Odigrane(ByVal igracId As Integer, ByVal sezonaId As Integer) As Integer
        selectStr = String.Format("SELECT Count(*) AS Odigrane FROM vSusreti WHERE IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Return DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
    End Function

    Private Function Pobjede(ByVal igracId As Integer, ByVal sezonaId As Integer) As Integer
        selectStr = String.Format("SELECT Count(*) AS Pobjede FROM vSusreti WHERE IgracId = {0} AND SezonaId = {1} AND Pobjednik = 1", igracId, sezonaId)
        Return DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
    End Function

    Private Function DobiveniGemovi(ByVal igracId As Integer, ByVal sezonaId As Integer) As Integer
        selectStr = String.Format("SELECT SUM(Set1) + Sum(Set2) FROM vSusretiProtivnik WHERE IgracId = {0} AND SezonaId = {1} ", igracId, sezonaId)
        Return DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
    End Function

    Private Function IzgubljeniGemovi(ByVal igracId As Integer, ByVal sezonaId As Integer) As Integer
        selectStr = String.Format("SELECT SUM(ProtivnikSet1) + Sum(ProtivnikSet2) FROM vSusretiProtivnik WHERE IgracId = {0} AND SezonaId = {1}", igracId, sezonaId)
        Return DataAccess.GetInt(GlobalTenisLiga.ConnectionString, selectStr)
    End Function
#End Region


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            If (Not IsPostBack) Then
                InitControls()
                GetTablica()
            End If
            hypRezultati.NavigateUrl = String.Format("ZadnjiRezultati.aspx?SezonaId={0}&LigaId={1}", ddlSezona.SelectedValue, ddlLiga.SelectedValue)

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
        End Sub


    Private Sub InitControls()

        selectStr = String.Format("SELECT * FROM Sezona WHERE SezonaNaziv <> 'Prijateljska' ORDER BY DatumOd DESC")
        ddlSezona.DataSource = DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr)
        ddlSezona.DataTextField = "SezonaNaziv"
        ddlSezona.DataValueField = "SezonaId"
        ddlSezona.DataBind()

        GetLige()

        If (Request.QueryString("LigaId") IsNot Nothing) Then
            ddlLiga.Items.FindByValue(Request.QueryString("LigaId")).Selected = True
        End If
        If (Request.QueryString("SezonaId") IsNot Nothing) Then
            ddlSezona.Items.FindByValue(Request.QueryString("SezonaId")).Selected = True
        End If

    End Sub

    Private Sub GetTablica()
        GetTable(Convert.ToInt16(ddlSezona.SelectedValue), Convert.ToInt16(ddlLiga.SelectedValue))
    End Sub



    Protected Sub ddlLiga_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlLiga.SelectedIndexChanged
        GetTablica()
        lblDomacinGost.Visible = False
    End Sub

    Protected Sub ddlSezona_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlSezona.SelectedIndexChanged
        GetLige()
        GetTablica()
        lblDomacinGost.Visible = False

    End Sub

    Private Sub GetLige()
        'selectStr = String.Format("SELECT DISTINCT LigaNaziv, LigaId FROM vSezonaLigaIgrac WHERE SezonaId = {0}", Request.QueryString("SezonaId"))
        selectStr = String.Format("SELECT DISTINCT LigaNaziv, LigaId FROM vSezonaLigaIgrac WHERE SezonaId = {0}", ddlSezona.SelectedValue)

        ddlLiga.DataSource = DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr)

        'Response.Write("<br>")
        'Response.Write(selectStr)
        'Response.Write("<br>")
        'Response.Write(GlobalTenisLiga.ConnectionString)

        ddlLiga.DataTextField = "LigaNaziv"
        ddlLiga.DataValueField = "LigaId"
        ddlLiga.DataBind()
    End Sub

    Protected Sub lnkDomacinGost_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkDomacinGost.Click
        Try
            lblDomacinGost.Text = "<table>"

            selectStr = String.Format("SELECT IgracId, ImePrezime FROM vSezonaLigaIgrac WHERE SezonaId = {0} AND LigaId = {1}", ddlSezona.SelectedValue, ddlLiga.SelectedValue)
            Dim dt As New DataTable()
            dt = DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr)

            If dt.Rows.Count = 4 Then


                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(1)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(2)("ImePrezime"))

                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(2)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(3)("ImePrezime"))

                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(3)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(0)("ImePrezime"))


            ElseIf dt.Rows.Count = 5 Then


                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(1)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(2)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(2)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(3)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(3)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(4)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(4)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(0)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(4)("ImePrezime"), dt.Rows(0)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(4)("ImePrezime"), dt.Rows(1)("ImePrezime"))

            ElseIf dt.Rows.Count = 6 Then

                '1-2, 1-3, 1-4
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(1)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(2)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(3)("ImePrezime"))

                '2-3, 2-4, 2-5
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(2)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(3)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(4)("ImePrezime"))

                '3-4, 3-5, 3-6
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(3)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(4)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(5)("ImePrezime"))

                '4-5, 4-6
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(4)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(5)("ImePrezime"))

                '5-6, 5-1
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(4)("ImePrezime"), dt.Rows(5)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(4)("ImePrezime"), dt.Rows(0)("ImePrezime"))

                '6-1, 6-2
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(5)("ImePrezime"), dt.Rows(0)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(5)("ImePrezime"), dt.Rows(1)("ImePrezime"))

            ElseIf dt.Rows.Count = 7 Then

                '1-2, 1-3, 1-4
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(1)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(2)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(0)("ImePrezime"), dt.Rows(3)("ImePrezime"))

                '2-3, 2-4, 2-5
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(2)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(3)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(1)("ImePrezime"), dt.Rows(4)("ImePrezime"))

                '3-4, 3-5, 3-6
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(3)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(4)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(2)("ImePrezime"), dt.Rows(5)("ImePrezime"))

                '4-5, 4-6, 4-7
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(4)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(5)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(3)("ImePrezime"), dt.Rows(6)("ImePrezime"))

                '5-6, 5-7, 5-1
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(4)("ImePrezime"), dt.Rows(5)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(4)("ImePrezime"), dt.Rows(6)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(4)("ImePrezime"), dt.Rows(0)("ImePrezime"))

                '6-7, 6-1, 6-2
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(5)("ImePrezime"), dt.Rows(6)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(5)("ImePrezime"), dt.Rows(0)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(5)("ImePrezime"), dt.Rows(1)("ImePrezime"))

                '7-1, 7-2, 7-3
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(6)("ImePrezime"), dt.Rows(0)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left>{0} - {1}</td></tr>", dt.Rows(6)("ImePrezime"), dt.Rows(1)("ImePrezime"))
                lblDomacinGost.Text += String.Format("<tr><td align=left bgcolor='#C0C0C0'>{0} - {1}</td></tr>", dt.Rows(6)("ImePrezime"), dt.Rows(2)("ImePrezime"))

            End If

            lblDomacinGost.Text += "</table>"

            lblDomacinGost.Visible = True

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub
End Class